-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE NTDBNEW
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Sushil Mittal
-- Create date: 
-- Description:	
-- =============================================
IF OBJECT_ID('sp_NTDBNEW_Imputation_ExtractRelevantColsFromAllTables14_BODYREGION_AISSCORE') IS NOT NULL
    DROP PROCEDURE sp_NTDBNEW_Imputation_ExtractRelevantColsFromAllTables14_BODYREGION_AISSCORE

GO
CREATE PROCEDURE sp_NTDBNEW_Imputation_ExtractRelevantColsFromAllTables14_BODYREGION_AISSCORE
	-- Add the parameters for the stored procedure here
	--@TableName NVARCHAR(100)
AS
BEGIN
	-- Insert statements for procedure here	SET NOCOUNT ON;
SELECT

/*DEMO_INC_KEY */ INC_KEY,
/*AISCCODE     */ AISCCODE_PREDOT, AISCCODE_SEVERITY, --BODYREGION is obtained FROM AISCCODE.PREDOT in Perl
/*DEMO         */ DEMO_AGE, DEMO_GENDER,
/*DCODE        */ DCODE_DCODE,
/*DISCHARGE    */ DISCHARGE_LOSDAYS, DISCHARGE_HOSPDISP,
/*ECODE        */ ECODE_ECODE, ECODE_ECODE2,
/*VITALS       */ VITALS_SBP, VITALS_RR, VITALS_PULSE, VITALS_GCSEYE, VITALS_GCSVERB, VITALS_GCSMOT, VITALS_GCSTOT,
/*ECODEDES     */ ECODEDES_INJTYPE,
/*ED           */ ED_EDDISP, ED_LOCATION, ED_YOINJ

FROM

AllTables14
--Filter based on Age first
WHERE

INC_KEY IS NOT NULL																						--MISSING
AND AISCCODE_PREDOT IS NOT NULL																							--MISSING
AND AISCCODE_SEVERITY IS NOT NULL					AND AISCCODE_SEVERITY != 9											--MISSING

AND DEMO_AGE IS NOT NULL																								--MISSING
/*
AND DEMO_GENDER IS NOT NULL	 																							--MISSING
AND DEMO_GENDER != 'Not Applicable BIU 1'																				--MISSING
AND DEMO_GENDER != 'Not Known BIU 2'																					--MISSING
AND DEMO_GENDER != 'Not Known/Not Recorded BIU 2'																		--MISSING
AND DEMO_GENDER != 'Not Recorded BIU 3'																					--MISSING
*/
AND DCODE_DCODE IS NOT NULL							AND DCODE_DCODE != 'null'											--MISSING

AND DISCHARGE_LOSDAYS IS NOT NULL					AND DISCHARGE_LOSDAYS >= 0											--MISSING
--DISCHARGE.HOSPDISP is first merged with ED.EDDISP and then filtered in Perl.

--ECODE.ECODE and ECODE.ECODE2 are merged and filtered in Perl.
/*
AND VITALS_SBP IS NOT NULL							AND VITALS_SBP != 'null'			AND VITALS_SBP >= 0				--MISSING
AND VITALS_RR IS NOT NULL							AND VITALS_RR != 'null'				AND VITALS_RR >= 0				--MISSING
AND VITALS_PULSE IS NOT NULL						AND VITALS_PULSE != 'null'			AND VITALS_PULSE >= 0			--MISSING
*/
AND VITALS_VSTYPE != 'EMS'
--ALL 4 GCS are filtered in Perl.
/*
AND ECODEDES_INJTYPE IS NOT NULL																						--MISSING
AND ECODEDES_INJTYPE != 'Activity Code - Not Valid as a Primary E-Code'													--MISSING
AND ECODEDES_INJTYPE != 'null'																							--MISSING
AND ECODEDES_INJTYPE != 'NULL'																							--MISSING
*/
AND ECODEDES_INJTYPE != 'Burn'																							--BURN/ NOT MISSING

--ED.EDDISP is first merged with DISCHARGE.HOSPDISP and then filtered in Perl.
/*
AND ED_LOCATION IS NOT NULL
AND ED_LOCATION != 'Not Applicable BIU 1'																				--MISSING
AND ED_LOCATION != 'Not Known BIU 2'																					--MISSING
AND ED_LOCATION != 'Not Known/Not Recorded BIU 2'																		--MISSING
AND ED_LOCATION != 'Not Recorded BIU 3'																					--MISSING
AND ED_LOCATION != 'Unspecified'	  																				    --MISSING
*/
END
GO
